﻿USE [HMSIndia]
GO

/****** Object:  View [dbo].[ViewEmpRegister]    Script Date: 09/07/2014 09:15:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create VIEW [dbo].[ViewEmpRegister]
AS
SELECT     dbo.EmpMaster.EmpNo, dbo.EmpMaster.FirstName, dbo.EmpMaster.LastName, dbo.EmpMaster.SurName, dbo.EmpMaster.Address, 
                      dbo.EmpMaster.[Telephone No] AS TelephoneNo, dbo.EmpMaster.[Mobile No] AS MobileNo, dbo.LookupsMaster.Name AS EmpType, dbo.EmpMaster.Gender, 
                      dbo.EmpMaster.Age
FROM         dbo.EmpMaster LEFT OUTER JOIN
                      dbo.LookupsMaster ON dbo.EmpMaster.EmpType = dbo.LookupsMaster.ID

GO


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


USE [HMSIndia]
GO

/****** Object:  View [dbo].[ViewDrRegister]    Script Date: 09/07/2014 09:15:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create VIEW [dbo].[ViewDrRegister]
AS
SELECT     dbo.DoctorMaster.FirstName, dbo.DoctorMaster.LastName, dbo.DoctorMaster.SurName, dbo.DoctorMaster.Address, dbo.DoctorMaster.TelephoneNo, 
                      dbo.DoctorMaster.MobileNo, dbo.DoctorMaster.Gender, dbo.DoctorMaster.Age, dbo.DoctorMaster.LicenseNo, dbo.LookupsMaster.Name AS Specialization, 
                      dbo.DoctorMaster.ConsultationCharge, LookupsMaster_1.Name AS DrType
FROM         dbo.DoctorMaster LEFT OUTER JOIN
                      dbo.LookupsMaster ON dbo.DoctorMaster.Specialization = dbo.LookupsMaster.ID LEFT OUTER JOIN
                      dbo.LookupsMaster AS LookupsMaster_1 ON dbo.DoctorMaster.DrType = LookupsMaster_1.ID

GO

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

USE [HMSIndia]
GO

/****** Object:  View [dbo].[ViewItemsServices]    Script Date: 09/07/2014 09:16:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create VIEW [dbo].[ViewItemsServices]
AS
SELECT     dbo.ItemServiceMaster.ItemNo, dbo.ItemServiceMaster.Name, dbo.LookupsMaster.Name AS UOM, dbo.ItemServiceMaster.Rate, 
                      LookupsMaster_2.Name AS SubCategory, dbo.ItemServiceMaster.Remarks, dbo.ItemServiceMaster.IsInventoryItem, LookupsMaster_1.Name AS Category, 
                      dbo.ItemServiceMaster.ROL
FROM         dbo.LookupsMaster AS LookupsMaster_1 RIGHT OUTER JOIN
                      dbo.LookupsMaster AS LookupsMaster_2 RIGHT OUTER JOIN
                      dbo.ItemServiceMaster INNER JOIN
                      dbo.LookupsMaster ON dbo.ItemServiceMaster.Unit = dbo.LookupsMaster.ID ON LookupsMaster_2.ID = dbo.ItemServiceMaster.SubCategoryID ON 
                      LookupsMaster_1.ID = dbo.ItemServiceMaster.CategoryID

GO

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

USE [HMSIndia]
GO

/****** Object:  View [dbo].[ViewBillRegister]    Script Date: 09/07/2014 09:17:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create VIEW [dbo].[ViewBillRegister]
AS
SELECT     dbo.PatientRegistration.PatientId, dbo.PatientRegistration.FirstName, dbo.PatientRegistration.LastName, dbo.PatientRegistration.SurName, dbo.BillingMaster.BillID, 
                      dbo.BillingMaster.BillDate, dbo.BillingMaster.NetAmt
FROM         dbo.BillingMaster INNER JOIN
                      dbo.PatientRegistration ON dbo.BillingMaster.PatientID = dbo.PatientRegistration.PatientId LEFT OUTER JOIN
                      dbo.LookupsMaster ON dbo.BillingMaster.BillCategory = dbo.LookupsMaster.ID

GO

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

USE [HMSIndia]
GO

/****** Object:  View [dbo].[ViewPatientRegister]    Script Date: 09/07/2014 09:17:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create VIEW [dbo].[ViewPatientRegister]
AS
SELECT     dbo.PatientRegistration.PatientId, dbo.PatientRegistration.FirstName, dbo.PatientRegistration.LastName, dbo.PatientRegistration.SurName, 
                      CASE dbo.PatientRegistration.Gender WHEN 0 THEN 'Male' ELSE 'Female' END AS Gender, dbo.PatientRegistration.Address, dbo.PatientRegistration.[Telephone No], 
                      dbo.PatientRegistration.[Mobile No], dbo.PatientRegistration.[Contact Person], dbo.PatientRegistration.Email, dbo.PatientRegistration.Remarks, 
                      dbo.PatientRegistration.PatientType, dbo.PatientRegistration.RegistrationDate, dbo.PatientRegistration.RefByDoc, dbo.PatientRegistration.DOB, 
                      dbo.PatientRegistration.Age, dbo.PatientRegistration.Occupation, dbo.LookupsMaster.Name AS MaritalStatus
FROM         dbo.PatientRegistration INNER JOIN
                      dbo.LookupsMaster ON dbo.PatientRegistration.MaritalStatus = dbo.LookupsMaster.ID

GO

